package cn.butcher21.dao;

import cn.butcher21.pojo.MyGoods;
import cn.butcher21.utils.DBUtil;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * butcher create in 2021/05/05/13:39
 * <p>
 * 商品查询类
 */
public class GoodsDao {
    public void add(MyGoods goods) throws SQLException, IOException, ClassNotFoundException {
        DBUtil dbUtil = new DBUtil();
        String sql = "INSERT INTO T_GOODS(G_ID, G_NAME, G_COUNT, G_PRICE, G_COST, U_NAME) VALUES (?,?,?,?,?,?)";
        PreparedStatement ps = dbUtil.getPs(sql);
        ps.setString(1, goods.getId());
        ps.setString(2, goods.getName());
        ps.setInt(3, goods.getCount());
        ps.setDouble(4, goods.getPrice());
        ps.setDouble(5, goods.getCost());
        ps.setString(6, goods.getInMan());
        ps.execute();
    }

    public void update(MyGoods goods) throws SQLException, IOException, ClassNotFoundException {
        DBUtil dbUtil = new DBUtil();
        String sql = "UPDATE T_GOODS SET G_NAME=?,G_COUNT=?,U_NAME=? WHERE G_ID=?";
        PreparedStatement ps = dbUtil.getPs(sql);
        ps.setString(1, goods.getName());
        ps.setInt(2, goods.getCount());
        ps.setString(3, goods.getInMan());
        ps.setString(4, goods.getId());
        ps.executeUpdate();
    }

    public void delete(String id) throws SQLException, IOException, ClassNotFoundException {
        DBUtil dbUtil = new DBUtil();
        String sql = "DELETE FROM T_GOODS WHERE G_ID = ?";
        PreparedStatement ps = dbUtil.getPs(sql);
        ps.setString(1, id);
        ps.executeUpdate();
    }

    public List<MyGoods> select() throws SQLException, IOException, ClassNotFoundException {
        List<MyGoods> list = new ArrayList<MyGoods>();
        DBUtil dbUtil = new DBUtil();
        String sql = "SELECT G_ID, G_NAME, G_COUNT, G_PRICE, G_COST, U_NAME FROM T_GOODS";
        PreparedStatement ps = dbUtil.getPs(sql);
        ResultSet rs = dbUtil.getRs(ps.executeQuery());
        while (rs.next()) {
            MyGoods goods = new MyGoods();
            goods.setId(rs.getString(1));
            goods.setName(rs.getString(2));
            goods.setCount(rs.getInt(3));
            goods.setPrice(rs.getDouble(4));
            goods.setCost(rs.getDouble(5));
            goods.setInMan(rs.getString(6));
            list.add(goods);
        }
        return list;
    }

    public List<MyGoods> select(String keyword) throws SQLException, IOException, ClassNotFoundException {
        List<MyGoods> list = new ArrayList<MyGoods>();
        DBUtil dbUtil = new DBUtil();
        String sql = "select G_ID,G_NAME,G_COUNT,G_PRICE,G_COST,U_NAME from t_goods where concat(G_ID,G_NAME,G_COUNT,G_PRICE,G_COST,U_NAME) LIKE ?";
        PreparedStatement ps = dbUtil.getPs(sql);
        ps.setString(1, "%" + keyword + "%");
        ResultSet rs = dbUtil.getRs(ps.executeQuery());
        while (rs.next()) {
            MyGoods goods = new MyGoods();
            goods.setId(rs.getString(1));
            goods.setName(rs.getString(2));
            goods.setCount(rs.getInt(3));
            goods.setPrice(rs.getDouble(4));
            goods.setCost(rs.getDouble(5));
            goods.setInMan(rs.getString(6));
            list.add(goods);
        }
        return list;
    }
}
